package com.shlh.saas.service.linkedin.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shlh.saas.common.Desensitization;
import com.shlh.saas.entity.linkedin.LinkedInUser;
import com.shlh.saas.entity.linkedin.LinkedInUserEs;
import com.shlh.saas.entity.twitter.TwitterUser;
import com.shlh.saas.entity.twitter.TwitterUserEs;
import com.shlh.saas.mapper.linkedin.LinkedInUserMapper;
import com.shlh.saas.model.dto.LinkedInQueryDTO;
import com.shlh.saas.service.linkedin.LinkedInEsService;
import com.shlh.saas.service.linkedin.LinkedInUserService;
import com.shlh.saas.util.LinkedInDataSource;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * LinkedIn用户服务实现类
 */
@Service
@Slf4j
public class LinkedInUserServiceImpl extends ServiceImpl<LinkedInUserMapper, LinkedInUser> implements LinkedInUserService {
    
    @Autowired
    private LinkedInUserMapper linkedInUserMapper;

    @Autowired
    private Desensitization desensitization;

    @Autowired
    private LinkedInEsService linkedInEsService;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private LinkedInDataSource linkedInDataSource;
    
    /**
     * 根据邮箱查询LinkedIn用户
     * @param email 邮箱
     * @return LinkedIn用户
     */
    @Override
    public List<LinkedInUser> findByEmail(String email) {
        List<Map<String, String>> maps = linkedInEsService.searchByEmail(email, 10, 0);

        List<LinkedInUser> linkedInUserList = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String[] split = map.get("mysqlTable").split("_");
            long mysqlTable = Long.parseLong(split[2]);
            long id = Long.parseLong(map.get("id"));
            LinkedInUser linkedInData = getLinkedInData(id, mysqlTable);
            linkedInUserList.add(linkedInData);
        }

        return linkedInUserList;

    }

    @Override
    public List<LinkedInUser> findByPhone(String phone) {
        List<Map<String, String>> maps = linkedInEsService.searchByPhone(phone, 100, 0);

        List<LinkedInUser> linkedInUserList = new ArrayList<>();

        for (Map<String, String> map : maps) {
            String[] split = map.get("mysqlTable").split("_");
            long mysqlTable = Long.parseLong(split[2]);
            long id = Long.parseLong(map.get("id"));
            LinkedInUser linkedInData = getLinkedInData(id, mysqlTable);
            linkedInUserList.add(linkedInData);
        }

        return linkedInUserList;
    }

    /**
     * 根据公司名称查询LinkedIn用户列表
     * @param companyName 公司名称
     * @return LinkedIn用户列表
     */
    @Override
    public List<LinkedInUser> findByCompanyName(String companyName) {
        LambdaQueryWrapper<LinkedInUser> wrapper = new LambdaQueryWrapper<>();
        wrapper.like(LinkedInUser::getCompanyName, companyName);
        return list(wrapper);
    }
    
    /**
     * 根据职位名称查询LinkedIn用户列表
     * @param jobTitle 职位名称
     * @return LinkedIn用户列表
     */
    @Override
    public List<LinkedInUser> findByJobTitle(String jobTitle) {
        LambdaQueryWrapper<LinkedInUser> wrapper = new LambdaQueryWrapper<>();
        wrapper.like(LinkedInUser::getJobTitle, jobTitle);
        return list(wrapper);
    }
    
    /**
     * 分页查询LinkedIn联系人
     * 
     * @param queryDTO 查询参数
     * @return 分页结果
     */
    public Page<LinkedInUser> getLinkedInDataPage(LinkedInQueryDTO queryDTO) {
        // 创建分页对象
        Page<LinkedInUser> page = new Page<>(queryDTO.getPageNum(), queryDTO.getPageSize());
        
        // 构建查询条件
        LambdaQueryWrapper<LinkedInUser> queryWrapper = new LambdaQueryWrapper<>();
        
        // 姓名模糊搜索
        if (StringUtils.hasText(queryDTO.getFullName())) {
            queryWrapper.like(LinkedInUser::getFullName, queryDTO.getFullName())
                       .or()
                       .like(LinkedInUser::getFirstName, queryDTO.getFullName())
                       .or()
                       .like(LinkedInUser::getLastName, queryDTO.getFullName());
        }
        
        // 公司名称模糊搜索
        if (StringUtils.hasText(queryDTO.getCompanyName())) {
            queryWrapper.like(LinkedInUser::getCompanyName, queryDTO.getCompanyName());
        }
        
        // 职位模糊搜索
        if (StringUtils.hasText(queryDTO.getJobTitle())) {
            queryWrapper.like(LinkedInUser::getJobTitle, queryDTO.getJobTitle());
        }
        
        // 行业筛选
        if (StringUtils.hasText(queryDTO.getIndustry())) {
            queryWrapper.like(LinkedInUser::getIndustry, queryDTO.getIndustry());
        }
        
        // 国家/地区筛选
        if (StringUtils.hasText(queryDTO.getCountry())) {
            queryWrapper.like(LinkedInUser::getLocationCountry, queryDTO.getCountry());
        }
        
        // 联系方式筛选
        if (StringUtils.hasText(queryDTO.getContactMethod())) {
            String[] methods = queryDTO.getContactMethod().split(",");
            
            // 将结果存储在最终变量中
            final boolean hasEmailContact = Arrays.asList(methods).contains("email");
            final boolean hasPhoneContact = Arrays.asList(methods).contains("phone");
            final boolean hasLinkedinContact = Arrays.asList(methods).contains("linkedin");
            
            queryWrapper.and(wrapper -> {
                boolean needsOr = false;
                
                if (hasEmailContact) {
                    wrapper.isNotNull(LinkedInUser::getEmails)
                            .ne(LinkedInUser::getEmails, "");
                    needsOr = true;
                }
                
                if (hasPhoneContact) {
                    if (needsOr) {
                        wrapper.or();
                    }
                    wrapper.isNotNull(LinkedInUser::getPhoneNumbers)
                           .ne(LinkedInUser::getPhoneNumbers, "");
                    needsOr = true;
                }
                
                if (hasLinkedinContact) {
                    if (needsOr) {
                        wrapper.or();
                    }
                    wrapper.isNotNull(LinkedInUser::getLinkedinUrl)
                           .ne(LinkedInUser::getLinkedinUrl, "");
                }
            });
        }
        
        // 公司规模筛选
        if (StringUtils.hasText(queryDTO.getCompanySize())) {
            String[] sizes = queryDTO.getCompanySize().split(",");
            if (sizes.length == 1) {
                queryWrapper.eq(LinkedInUser::getCompanySize, sizes[0]);
            } else {
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < sizes.length; i++) {
                        String size = sizes[i];
                        if (i > 0) {
                            wrapper.or();
                        }
                        wrapper.eq(LinkedInUser::getCompanySize, size);
                    }
                });
            }
        }
        
        // 排序
        queryWrapper.orderByDesc(LinkedInUser::getId);
        
        // 执行分页查询
        Page<LinkedInUser> linkIn = page(page, queryWrapper);

        for (LinkedInUser linkedInUser : linkIn.getRecords()) {
            // 敏感信息脱敏
            if (linkedInUser.getEmails() != null){
                linkedInUser.setEmails(desensitization.maskEmail(linkedInUser.getEmails()));
            }

            if (linkedInUser.getPhoneNumbers() != null){
                linkedInUser.setPhoneNumbers(desensitization.maskPhone(linkedInUser.getPhoneNumbers()));
            }

            if (linkedInUser.getMobile() != null){
                linkedInUser.setMobile(desensitization.maskPhone(linkedInUser.getMobile()));
            }

            if (linkedInUser.getLinkedinUrl() != null){
                linkedInUser.setLinkedinUrl(desensitization.maskWebAddress(linkedInUser.getLinkedinUrl()));
            }
        }
        return linkIn;
    }

    /**
     * 根据ID获取联系人详情
     * 
     * @param id 联系人ID
     * @return 联系人信息
     */
    public LinkedInUser getLinkedInDataById(Integer id) {
        return getById(id);
    }

    /**
     * 导出LinkedIn联系人数据
     * 
     * @param queryDTO 查询参数
     * @return 导出的数据列表
     */
    public List<LinkedInUser> exportLinkedInData(LinkedInQueryDTO queryDTO) {
        // 构建查询条件
        LambdaQueryWrapper<LinkedInUser> queryWrapper = new LambdaQueryWrapper<>();
        
        // 姓名模糊搜索
        if (StringUtils.hasText(queryDTO.getFullName())) {
            queryWrapper.like(LinkedInUser::getFullName, queryDTO.getFullName())
                       .or()
                       .like(LinkedInUser::getFirstName, queryDTO.getFullName())
                       .or()
                       .like(LinkedInUser::getLastName, queryDTO.getFullName());
        }
        
        // 公司名称模糊搜索
        if (StringUtils.hasText(queryDTO.getCompanyName())) {
            queryWrapper.like(LinkedInUser::getCompanyName, queryDTO.getCompanyName());
        }
        
        // 职位模糊搜索
        if (StringUtils.hasText(queryDTO.getJobTitle())) {
            queryWrapper.like(LinkedInUser::getJobTitle, queryDTO.getJobTitle());
        }
        
        // 行业筛选
        if (StringUtils.hasText(queryDTO.getIndustry())) {
            queryWrapper.like(LinkedInUser::getIndustry, queryDTO.getIndustry());
        }
        
        // 国家/地区筛选
        if (StringUtils.hasText(queryDTO.getCountry())) {
            queryWrapper.like(LinkedInUser::getLocationCountry, queryDTO.getCountry());
        }
        
        // 联系方式筛选
        if (StringUtils.hasText(queryDTO.getContactMethod())) {
            String[] methods = queryDTO.getContactMethod().split(",");
            
            // 将结果存储在最终变量中
            final boolean hasEmailContact = Arrays.asList(methods).contains("email");
            final boolean hasPhoneContact = Arrays.asList(methods).contains("phone");
            final boolean hasLinkedinContact = Arrays.asList(methods).contains("linkedin");
            
            queryWrapper.and(wrapper -> {
                boolean needsOr = false;
                
                if (hasEmailContact) {
                    wrapper.isNotNull(LinkedInUser::getEmails)
                            .ne(LinkedInUser::getEmails, "");
                    needsOr = true;
                }
                
                if (hasPhoneContact) {
                    if (needsOr) {
                        wrapper.or();
                    }
                    wrapper.isNotNull(LinkedInUser::getPhoneNumbers)
                           .ne(LinkedInUser::getPhoneNumbers, "");
                    needsOr = true;
                }
                
                if (hasLinkedinContact) {
                    if (needsOr) {
                        wrapper.or();
                    }
                    wrapper.isNotNull(LinkedInUser::getLinkedinUrl)
                           .ne(LinkedInUser::getLinkedinUrl, "");
                }
            });
        }
        
        // 处理公司规模筛选
        if (StringUtils.hasText(queryDTO.getCompanySize())) {
            String[] sizes = queryDTO.getCompanySize().split(",");
            if (sizes.length == 1) {
                queryWrapper.eq(LinkedInUser::getCompanySize, sizes[0]);
            } else {
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < sizes.length; i++) {
                        String size = sizes[i];
                        if (i > 0) {
                            wrapper.or();
                        }
                        wrapper.eq(LinkedInUser::getCompanySize, size);
                    }
                });
            }
        }
        
        // 限制导出的数据量
        if (queryDTO.getLimit() != null && queryDTO.getLimit() > 0) {
            queryWrapper.last("LIMIT " + queryDTO.getLimit());
        } else {
            queryWrapper.last("LIMIT 5000"); // 默认限制为5000条
        }
        
        List<LinkedInUser> dataList = list(queryWrapper);
        
        return dataList;
    }
    
    /**
     * 在所有字段中搜索关键词
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的数据列表
     */
    public List<LinkedInUser> searchByKeywordInAllFields(String keyword, int offset, int limit) {
        log.info("在LinkedIn数据所有字段中搜索关键词: {}, 偏移量: {}, 限制数: {}", keyword, offset, limit);
        try {
            if (!StringUtils.hasText(keyword)) {
                // 如果关键词为空，返回所有数据（分页）
                return linkedInUserMapper.selectAllWithPagination(offset, limit);
            }
            
            // 在所有字段中搜索关键词
            return linkedInUserMapper.searchInAllFieldsWithPagination(keyword, offset, limit);
        } catch (Exception e) {
            log.error("LinkedIn数据全字段搜索失败", e);
            return new ArrayList<>();
        }
    }
    
    /**
     * 统计所有字段中包含关键词的记录数量
     * 
     * @param keyword 搜索关键词
     * @return 记录总数
     */
    public long countByKeywordInAllFields(String keyword) {
        log.info("统计LinkedIn数据中包含关键词: {} 的记录数", keyword);
        try {
            if (!StringUtils.hasText(keyword)) {
                // 如果关键词为空，返回总数
                return linkedInUserMapper.countAll();
            }
            
            // 统计在所有字段中包含关键词的记录总数
            return linkedInUserMapper.countInAllFields(keyword);
        } catch (Exception e) {
            log.error("统计LinkedIn数据全字段搜索结果数量失败", e);
            return 0;
        }
    }
    
    /**
     * 获取搜索建议
     * 
     * @param keyword 关键词
     * @return 搜索建议列表
     */
    public List<String> getSuggestions(String keyword) {
        log.info("获取LinkedIn数据搜索建议，关键词: {}", keyword);
        if (!StringUtils.hasText(keyword) || keyword.length() < 2) {
            return new ArrayList<>();
        }
        
        try {
            // 从姓名中查找
            List<String> fullNames = linkedInUserMapper.findSimilarFullNames(keyword);
            
            // 从公司中查找
            List<String> companies = linkedInUserMapper.findSimilarCompanies(keyword);
            
            // 从行业中查找
            List<String> industries = linkedInUserMapper.findSimilarIndustries(keyword);
            
            // 合并结果并去重
            List<String> suggestions = new ArrayList<>();
            suggestions.addAll(fullNames);
            suggestions.addAll(companies);
            suggestions.addAll(industries);
            
            // 去重并限制数量
            return suggestions.stream()
                    .distinct()
                    .limit(10)
                    .toList();
        } catch (Exception e) {
            log.error("获取LinkedIn数据搜索建议失败", e);
            return new ArrayList<>();
        }
    }
    
    /**
     * 获取符合条件的LinkedIn数据数量
     * 
     * @param queryDTO 查询参数
     * @return 符合条件的记录数
     */
    @Override
    public long countLinkedInData(LinkedInQueryDTO queryDTO) {
        log.info("计算符合条件的LinkedIn数据数量，参数: {}", queryDTO);
        
        try {
            // 构建查询条件
            LambdaQueryWrapper<LinkedInUser> queryWrapper = new LambdaQueryWrapper<>();
            
            // 姓名模糊搜索
            if (StringUtils.hasText(queryDTO.getFullName())) {
                queryWrapper.like(LinkedInUser::getFullName, queryDTO.getFullName())
                           .or()
                           .like(LinkedInUser::getFirstName, queryDTO.getFullName())
                           .or()
                           .like(LinkedInUser::getLastName, queryDTO.getFullName());
            }
            
            // 公司名称模糊搜索
            if (StringUtils.hasText(queryDTO.getCompanyName())) {
                queryWrapper.like(LinkedInUser::getCompanyName, queryDTO.getCompanyName());
            }
            
            // 职位模糊搜索
            if (StringUtils.hasText(queryDTO.getJobTitle())) {
                queryWrapper.like(LinkedInUser::getJobTitle, queryDTO.getJobTitle());
            }
            
            // 行业筛选
            if (StringUtils.hasText(queryDTO.getIndustry())) {
                queryWrapper.like(LinkedInUser::getIndustry, queryDTO.getIndustry());
            }
            
            // 国家/地区筛选
            if (StringUtils.hasText(queryDTO.getCountry())) {
                queryWrapper.like(LinkedInUser::getLocationCountry, queryDTO.getCountry());
            }
            
            // 联系方式筛选
            if (StringUtils.hasText(queryDTO.getContactMethod())) {
                String[] methods = queryDTO.getContactMethod().split(",");
                
                // 将结果存储在最终变量中
                final boolean hasEmailContact = Arrays.asList(methods).contains("email");
                final boolean hasPhoneContact = Arrays.asList(methods).contains("phone");
                final boolean hasLinkedinContact = Arrays.asList(methods).contains("linkedin");
                
                queryWrapper.and(wrapper -> {
                    boolean needsOr = false;
                    
                    if (hasEmailContact) {
                        wrapper.isNotNull(LinkedInUser::getEmails)
                                .ne(LinkedInUser::getEmails, "");
                        needsOr = true;
                    }
                    
                    if (hasPhoneContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getPhoneNumbers)
                               .ne(LinkedInUser::getPhoneNumbers, "");
                        needsOr = true;
                    }
                    
                    if (hasLinkedinContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getLinkedinUrl)
                               .ne(LinkedInUser::getLinkedinUrl, "");
                    }
                });
            }
            
            // 公司规模筛选
            if (StringUtils.hasText(queryDTO.getCompanySize())) {
                String[] sizes = queryDTO.getCompanySize().split(",");
                if (sizes.length == 1) {
                    queryWrapper.eq(LinkedInUser::getCompanySize, sizes[0]);
                } else {
                    queryWrapper.and(wrapper -> {
                        for (int i = 0; i < sizes.length; i++) {
                            String size = sizes[i];
                            if (i > 0) {
                                wrapper.or();
                            }
                            wrapper.eq(LinkedInUser::getCompanySize, size);
                        }
                    });
                }
            }
            
            // 工作年限筛选
            if (StringUtils.hasText(queryDTO.getYearsExperience())) {
                String[] years = queryDTO.getYearsExperience().split(",");
                if (years.length == 1) {
                    queryWrapper.eq(LinkedInUser::getYearsExperience, years[0]);
                } else {
                    queryWrapper.and(wrapper -> {
                        for (int i = 0; i < years.length; i++) {
                            String year = years[i];
                            if (i > 0) {
                                wrapper.or();
                            }
                            wrapper.eq(LinkedInUser::getYearsExperience, year);
                        }
                    });
                }
            }
            
            // 推断薪资筛选
            if (StringUtils.hasText(queryDTO.getInferredSalary())) {
                String[] salaries = queryDTO.getInferredSalary().split(",");
                if (salaries.length == 1) {
                    queryWrapper.eq(LinkedInUser::getInferredSalary, salaries[0]);
                } else {
                    queryWrapper.and(wrapper -> {
                        for (int i = 0; i < salaries.length; i++) {
                            String salary = salaries[i];
                            if (i > 0) {
                                wrapper.or();
                            }
                            wrapper.eq(LinkedInUser::getInferredSalary, salary);
                        }
                    });
                }
            }
            
            // 邮箱状态筛选
            if (StringUtils.hasText(queryDTO.getEmailStatus())) {
                if ("withEmail".equals(queryDTO.getEmailStatus())) {
                    queryWrapper.isNotNull(LinkedInUser::getEmails)
                               .ne(LinkedInUser::getEmails, "");
                } else if ("withoutEmail".equals(queryDTO.getEmailStatus())) {
                    queryWrapper.and(wrapper -> 
                        wrapper.isNull(LinkedInUser::getEmails)
                              .or()
                              .eq(LinkedInUser::getEmails, "")
                    );
                }
            }
            
            // 执行统计查询
            return count(queryWrapper);
        } catch (Exception e) {
            log.error("计算符合条件的LinkedIn数据数量失败", e);
            return 0;
        }
    }

    /**
     * 带筛选条件的关键词搜索
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @param companyName 公司名称筛选
     * @param jobTitle 职位筛选
     * @param industry 行业筛选
     * @param country 国家/地区筛选
     * @param contactMethod 联系方式筛选
     * @param companySize 公司规模筛选
     * @return 符合条件的数据列表
     */
    @Override
    public List<LinkedInUser> searchByKeywordWithFilters(String keyword, int offset, int limit, 
                                                     String companyName, String jobTitle, String industry,
                                                     String country, String contactMethod, String companySize, String companyFounded) {
        log.info("在LinkedIn数据中搜索关键词: {}, 筛选条件: 公司={}, 职位={}, 行业={}, 国家={}, 联系方式={}, 公司规模={}",
                keyword, companyName, jobTitle, industry, country, contactMethod, companySize);
        try {
            // 构建查询条件
            LambdaQueryWrapper<LinkedInUser> queryWrapper = new LambdaQueryWrapper<>();
            
            // 关键词搜索
            if (StringUtils.hasText(keyword)) {
                queryWrapper.and(wrapper -> wrapper
                    .like(LinkedInUser::getFullName, keyword)
                    .or().like(LinkedInUser::getFirstName, keyword)
                    .or().like(LinkedInUser::getLastName, keyword)
                    .or().like(LinkedInUser::getCompanyName, keyword)
                    .or().like(LinkedInUser::getJobTitle, keyword)
                    .or().like(LinkedInUser::getEmails, keyword)
                    .or().like(LinkedInUser::getPhoneNumbers, keyword)
                    .or().like(LinkedInUser::getMobile, keyword)
                    .or().like(LinkedInUser::getIndustry, keyword));
            }
            
            // 添加筛选条件
            
            // 公司名称筛选
            if (StringUtils.hasText(companyName)) {
                String[] companies = companyName.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < companies.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getCompanyName, companies[i].trim());
                    }
                });
            }
            
            // 职位筛选
            if (StringUtils.hasText(jobTitle)) {
                String[] titles = jobTitle.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < titles.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getJobTitle, titles[i].trim());
                    }
                });
            }
            
            // 行业筛选
            if (StringUtils.hasText(industry)) {
                String[] industries = industry.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < industries.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getIndustry, industries[i].trim());
                    }
                });
            }
            
            // 国家/地区筛选
            if (StringUtils.hasText(country)) {
                String[] countries = country.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < countries.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getLocationCountry, countries[i].trim());
                    }
                });
            }
            
            // 联系方式筛选
            if (StringUtils.hasText(contactMethod)) {
                String[] methods = contactMethod.split(",");
                
                final boolean hasEmailContact = Arrays.asList(methods).contains("email");
                final boolean hasPhoneContact = Arrays.asList(methods).contains("phone");
                final boolean hasLinkedinContact = Arrays.asList(methods).contains("linkedin");
                final boolean hasCompanyWebsite = Arrays.asList(methods).contains("companyWebsite");
                final boolean hasFaceBookUrl = Arrays.asList(methods).contains("facebookUrl");
                final boolean hasTwitterUrl = Arrays.asList(methods).contains("twitterUrl");
                final boolean hasGithubUrl = Arrays.asList(methods).contains("githubUrl");


                queryWrapper.and(wrapper -> {
                    boolean needsOr = false;
                    
                    if (hasEmailContact) {
                        wrapper.isNotNull(LinkedInUser::getEmails)
                                .ne(LinkedInUser::getEmails, "");
                        needsOr = true;
                    }
                    
                    if (hasPhoneContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getMobile)
                               .ne(LinkedInUser::getMobile, "");
                        needsOr = true;
                    }
                    
                    if (hasLinkedinContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getLinkedinUrl)
                               .ne(LinkedInUser::getLinkedinUrl, "");
                        needsOr = true;
                    }

                    if (hasCompanyWebsite) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getCompanyWebsite)
                               .ne(LinkedInUser::getCompanyWebsite, "");
                        needsOr = true;
                    }

                    if (hasGithubUrl) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getGithubUrl)
                                .ne(LinkedInUser::getGithubUrl, "");
                        needsOr = true;
                    }

                    if (hasTwitterUrl) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getGithubUrl)
                                .ne(LinkedInUser::getGithubUrl, "");
                        needsOr = true;
                    }

                    if (hasFaceBookUrl) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getGithubUrl)
                                .ne(LinkedInUser::getGithubUrl, "");
                    }
                });
            }
            
            // 公司规模筛选
            if (StringUtils.hasText(companySize)) {
                String[] sizes = companySize.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < sizes.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.eq(LinkedInUser::getCompanySize, sizes[i].trim());
                    }
                });
            }

            // 公司成立时间筛选
            if (StringUtils.hasText(companyFounded)) {
                if (companyFounded.contains(",")) {
                    String[] years = companyFounded.split(",");
                    if (years.length > 0) {
                        queryWrapper.and(wrapper -> {
                            for (String s : years) {
                                // 对于每个条件，使用or连接
                                wrapper.or(w -> extractedForCondition(s, w));
                            }
                        });
                    }
                } else {
                    extractedForCondition(companyFounded, queryWrapper);
                }
            }
            
            // 排序
            queryWrapper.orderByDesc(LinkedInUser::getId);
            
            // 分页查询
            Page<LinkedInUser> page = new Page<>(offset / limit + 1, limit);
            Page<LinkedInUser> resultPage = page(page, queryWrapper);

            for (LinkedInUser linkedInUser : resultPage.getRecords()) {
                // 敏感信息脱敏
                desensitizationLinkedIn(linkedInUser);
            }
            return resultPage.getRecords();
        } catch (Exception e) {
            log.error("LinkedIn数据筛选搜索失败", e);
            return new ArrayList<>();
        }
    }

    private void desensitizationLinkedIn(LinkedInUser linkedInUser) {
        if (linkedInUser.getEmails() != null){
            linkedInUser.setEmails(desensitization.maskEmail(linkedInUser.getEmails()));
        }

        if (linkedInUser.getPhoneNumbers() != null){
            linkedInUser.setPhoneNumbers(desensitization.maskPhone(linkedInUser.getPhoneNumbers()));
        }

        if (linkedInUser.getMobile() != null){
            linkedInUser.setMobile(desensitization.maskPhone(linkedInUser.getMobile()));
        }

        if (linkedInUser.getLinkedinUrl() != null){
            linkedInUser.setLinkedinUrl(desensitization.maskWebAddress(linkedInUser.getLinkedinUrl()));
        }

        if (linkedInUser.getCompanyWebsite() != null){
            linkedInUser.setCompanyWebsite(desensitization.maskWebAddress(linkedInUser.getCompanyWebsite()));
        }
    }

    private void extractedForCondition(String companyFounded, LambdaQueryWrapper<LinkedInUser> queryWrapper) {
        if (companyFounded.contains("_")) {
            String[] year = companyFounded.split("_");
            if (Objects.equals(year[0], "after")) {
                // 添加大于等于的条件
                queryWrapper.ge(LinkedInUser::getCompanyFounded, year[1]);
            } else if (Objects.equals(year[0], "before")) {
                // 添加小于等于的条件
                queryWrapper.le(LinkedInUser::getCompanyFounded, year[1]);
            } else if (Objects.equals(year[0], "year")) {
                // 添加等于的条件
                queryWrapper.eq(LinkedInUser::getCompanyFounded, year[1]);
            } else {
                // 添加范围的条件
                queryWrapper.between(LinkedInUser::getCompanyFounded, year[0], year[1]);
            }
        } else {
            // 添加等于的条件
            queryWrapper.eq(LinkedInUser::getCompanyFounded, companyFounded.trim());
        }
    }

    /**
     * 统计满足筛选条件和关键词的记录数量
     * 
     * @param keyword 搜索关键词
     * @param companyName 公司名称筛选
     * @param jobTitle 职位筛选
     * @param industry 行业筛选
     * @param country 国家/地区筛选
     * @param contactMethod 联系方式筛选
     * @param companySize 公司规模筛选
     * @return 记录总数
     */
    @Override
    public long countByKeywordWithFilters(String keyword, String companyName, String jobTitle, String industry,
                                      String country, String contactMethod, String companySize) {
        log.info("统计LinkedIn数据中搜索词: {}, 筛选条件: 公司={}, 职位={}, 行业={}, 国家={}, 联系方式={}, 公司规模={}的记录数",
                keyword, companyName, jobTitle, industry, country, contactMethod, companySize);
                
        try {
            // 构建查询条件
            LambdaQueryWrapper<LinkedInUser> queryWrapper = new LambdaQueryWrapper<>();
            
            // 关键词搜索
            if (StringUtils.hasText(keyword)) {
                queryWrapper.and(wrapper -> wrapper
                    .like(LinkedInUser::getFullName, keyword)
                    .or().like(LinkedInUser::getFirstName, keyword)
                    .or().like(LinkedInUser::getLastName, keyword)
                    .or().like(LinkedInUser::getCompanyName, keyword)
                    .or().like(LinkedInUser::getJobTitle, keyword)
                    .or().like(LinkedInUser::getEmails, keyword)
                    .or().like(LinkedInUser::getPhoneNumbers, keyword)
                    .or().like(LinkedInUser::getMobile, keyword)
                    .or().like(LinkedInUser::getIndustry, keyword));
            }
            
            // 添加筛选条件
            
            // 公司名称筛选
            if (StringUtils.hasText(companyName)) {
                String[] companies = companyName.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < companies.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getCompanyName, companies[i].trim());
                    }
                });
            }
            
            // 职位筛选
            if (StringUtils.hasText(jobTitle)) {
                String[] titles = jobTitle.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < titles.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getJobTitle, titles[i].trim());
                    }
                });
            }
            
            // 行业筛选
            if (StringUtils.hasText(industry)) {
                String[] industries = industry.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < industries.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getIndustry, industries[i].trim());
                    }
                });
            }
            
            // 国家/地区筛选
            if (StringUtils.hasText(country)) {
                String[] countries = country.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < countries.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.like(LinkedInUser::getLocationCountry, countries[i].trim());
                    }
                });
            }
            
            // 联系方式筛选
            if (StringUtils.hasText(contactMethod)) {
                String[] methods = contactMethod.split(",");
                
                final boolean hasEmailContact = Arrays.asList(methods).contains("email");
                final boolean hasPhoneContact = Arrays.asList(methods).contains("phone");
                final boolean hasLinkedinContact = Arrays.asList(methods).contains("linkedin");
                
                queryWrapper.and(wrapper -> {
                    boolean needsOr = false;
                    
                    if (hasEmailContact) {
                        wrapper.isNotNull(LinkedInUser::getEmails)
                                .ne(LinkedInUser::getEmails, "");
                        needsOr = true;
                    }
                    
                    if (hasPhoneContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getMobile)
                               .ne(LinkedInUser::getMobile, "");
                        needsOr = true;
                    }
                    
                    if (hasLinkedinContact) {
                        if (needsOr) {
                            wrapper.or();
                        }
                        wrapper.isNotNull(LinkedInUser::getLinkedinUrl)
                               .ne(LinkedInUser::getLinkedinUrl, "");
                    }
                });
            }
            
            // 公司规模筛选
            if (StringUtils.hasText(companySize)) {
                String[] sizes = companySize.split(",");
                queryWrapper.and(wrapper -> {
                    for (int i = 0; i < sizes.length; i++) {
                        if (i > 0) wrapper.or();
                        wrapper.eq(LinkedInUser::getCompanySize, sizes[i].trim());
                    }
                });
            }
            
            // 统计记录数
            return count(queryWrapper);
        } catch (Exception e) {
            log.error("统计LinkedIn数据筛选结果数量失败", e);
            return 0;
        }
    }

    /**
     * 获取LinkedIn联系人指定字段的完整信息（未脱敏）
     *
     * @param contactId 联系人ID
     * @param fieldName 字段名称
     * @return 完整的字段信息
     */
    @Override
    public String getFullContactInfo(Long contactId, Long mt, String fieldName) {
        try {
            log.info("获取LinkedIn联系人完整信息，联系人ID: {}, 字段名: {}", contactId, fieldName);

            // 根据ID查询联系人
            LinkedInUser linkedInUser = getLinkedInData(contactId, mt);
            if (linkedInUser == null) {
                log.warn("LinkedIn联系人不存在，ID: {}", contactId);
                return null;
            }

            // 根据字段名获取对应的完整信息（未脱敏）
            String fieldValue = null;
            switch (fieldName) {
                case "emails":
                    fieldValue = linkedInUser.getEmails();
                    break;
                case "mobile":
                    fieldValue = linkedInUser.getMobile();
                    break;
                case "phoneNumbers":
                    fieldValue = linkedInUser.getPhoneNumbers();
                    break;
                case "linkedinUrl":
                    fieldValue = linkedInUser.getLinkedinUrl();
                    break;
                case "companyWebsite":
                    fieldValue = linkedInUser.getCompanyWebsite();
                    break;
                case "twitterUserName":
                    fieldValue = linkedInUser.getTwitterUserName();
                    break;
                case "twitterUrl":
                    fieldValue = linkedInUser.getTwitterUrl();
                    break;
                case "githubUrl":
                    fieldValue = linkedInUser.getGithubUrl();
                    break;
                case "facebookUrl":
                    fieldValue = linkedInUser.getFacebookUrl();
                    break;
                default:
                    log.warn("不支持的字段名: {}", fieldName);
                    return null;
            }

            if (fieldValue == null || fieldValue.trim().isEmpty()) {
                log.warn("字段值为空，联系人ID: {}, 字段名: {}", contactId, fieldName);
                return null;
            }

            log.info("成功获取LinkedIn联系人完整信息，联系人ID: {}, 字段名: {}", contactId, fieldName);
            return fieldValue;

        } catch (Exception e) {
            log.error("获取LinkedIn联系人完整信息失败，联系人ID: {}, 字段名: {}", contactId, fieldName, e);
            throw new RuntimeException("获取联系人完整信息失败: " + e.getMessage(), e);
        }
    }

    private LinkedInUser getLinkedInData(Long contactId, Long mt) {

        String tableName = "ly_user_" + mt;

        List<String> recordId = new ArrayList<>();

        recordId.add(contactId.toString());

        List<LinkedInUser> linkedInUsers = queryCompleteDataByTableAndIds(tableName, recordId);

        return linkedInUsers.get(0);
    }
    
    /**
     * 批量获取LinkedIn详情（按表号和ID列表）
     * 
     * @param tableNum 表号
     * @param ids ID列表
     * @return LinkedIn用户列表
     */
    private List<LinkedInUser> batchGetLinkedInDataByTableAndIds(Long tableNum, List<Long> ids) {
        long startTime = System.currentTimeMillis();
        log.info("批量查询表ly_user_{}: 包含{}条记录", tableNum, ids.size());
        
        try {
            String tableName = "ly_user_" + tableNum;
            
            // 将Long转为String（queryCompleteDataByTableAndIds需要String列表）
            List<String> idStrings = ids.stream()
                .map(String::valueOf)
                .collect(Collectors.toList());
            
            List<LinkedInUser> results = queryCompleteDataByTableAndIds(tableName, idStrings);
            
            long endTime = System.currentTimeMillis();
            log.info("批量查询表ly_user_{}完成: 请求{}条, 成功{}条, 耗时{}ms", 
                     tableNum, ids.size(), results.size(), (endTime - startTime));
            
            return results;
            
        } catch (Exception e) {
            log.error("批量查询表ly_user_{}失败: ids数量={}", tableNum, ids.size(), e);
            return new ArrayList<>();
        }
    }

    // ==================== ES查询方法实现 ====================

    @Override
    public Page<LinkedInUserEs> getLinkedInDataPageFromES(LinkedInQueryDTO queryDTO) {
        log.info("ES分页查询LinkedIn用户列表: {}", queryDTO);

        try {
            // 构建ES查询条件
            LinkedInUserEs linkedInUserEs = buildLinkedInUserEs(queryDTO);

            // 设置分页参数
            Integer page = queryDTO.getPage();
            Integer size = queryDTO.getSize();

            // 执行ES查询
            Page<LinkedInUserEs> esPage = linkedInEsService.queryLinkedInUserList(linkedInUserEs, page, size);

            log.info("ES查询完成，返回 {} 条记录，总数: {}", esPage.getRecords().size(), esPage.getTotal());
            return esPage;

        } catch (Exception e) {
            log.error("ES分页查询LinkedIn用户失败", e);
            // 返回空结果页面
            Page<LinkedInUserEs> emptyPage = new Page<>();
            emptyPage.setCurrent(queryDTO.getPage());
            emptyPage.setSize(queryDTO.getPageSize());
            emptyPage.setTotal(0);
            emptyPage.setRecords(new ArrayList<>());
            return emptyPage;
        }
    }

    @Override
    public long countLinkedInDataFromES(LinkedInQueryDTO queryDTO) {
        log.info("ES统计LinkedIn用户数量: {}", queryDTO);

        try {
            // 构建ES查询条件
            LinkedInUserEs linkedInUserEs = buildLinkedInUserEs(queryDTO);

            // 执行ES计数查询
            long count = linkedInEsService.countLinkedInUsers(linkedInUserEs);

            log.info("ES计数查询完成，总数: {}", count);
            return count;

        } catch (Exception e) {
            log.error("ES统计LinkedIn用户数量失败", e);
            return 0L;
        }
    }

    @Override
    public List<LinkedInUser> exportLinkedInDataFromES(LinkedInQueryDTO queryDTO) {
        log.info("ES导出LinkedIn用户数据（两阶段查询）: {}", queryDTO);

        try {
            // 第一阶段：从ES获取标识信息
            int limit = queryDTO.getLimit() != null ? queryDTO.getLimit() : 10000;
            List<Map<String, String>> identifiers = linkedInEsService.searchForExport(queryDTO, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                log.info("ES查询未找到符合条件的数据");
                return new ArrayList<>();
            }

            // 第二阶段：根据标识信息从数据库查询完整数据
            // 对于大数据量导出，使用优化的批处理方法
            List<LinkedInUser> completeData;
            if (identifiers.size() > 10000) {
                log.info("检测到大数据量导出({}条)，使用批处理优化方式查询数据库", identifiers.size());
                completeData = getCompleteDataFromDBOptimized(identifiers);
            } else {
                completeData = getCompleteDataFromDB(identifiers);
            }
            
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());
            return completeData;

        } catch (Exception e) {
            log.error("ES导出LinkedIn用户数据失败", e);
            return new ArrayList<>();
        }
    }
    
    /**
     * 使用批处理优化方式从数据库查询完整数据
     * 将大量查询拆分为按表分组的批处理查询，提高查询性能
     */
    private List<LinkedInUser> getCompleteDataFromDBOptimized(List<Map<String, String>> identifiers) {
        log.info("开始批量优化查询数据库，共 {} 条标识", identifiers.size());
        
        // 按表名分组
        Map<String, List<String>> tableToIds = new HashMap<>();
        for (Map<String, String> identifier : identifiers) {
            String tableName = identifier.get("mysqlTable");
            String id = identifier.get("id");
            
            if (tableName != null && id != null) {
                tableToIds.computeIfAbsent(tableName, k -> new ArrayList<>()).add(id);
            }
        }
        
        log.info("标识按表分组完成，共 {} 个不同表", tableToIds.size());
        
        // 创建线程池进行并行查询
        int processors = Runtime.getRuntime().availableProcessors();
        ExecutorService executorService = Executors.newFixedThreadPool(
                Math.min(processors, tableToIds.size())); 
        
        List<CompletableFuture<List<LinkedInUser>>> futures = new ArrayList<>();
        
        // 对每个表进行批量查询
        for (Map.Entry<String, List<String>> entry : tableToIds.entrySet()) {
            String tableName = entry.getKey();
            List<String> ids = entry.getValue();
            
            // 分批处理每个表的数据，每批次1000条
            int batchSize = 1000;
            for (int i = 0; i < ids.size(); i += batchSize) {
                final int batchStart = i;
                final int batchEnd = Math.min(i + batchSize, ids.size());
                final List<String> batchIds = ids.subList(batchStart, batchEnd);
                
                CompletableFuture<List<LinkedInUser>> future = CompletableFuture.supplyAsync(() -> {
                    log.info("开始查询表 {}，批次大小: {}", tableName, batchIds.size());
                    try {
                        List<LinkedInUser> result = queryCompleteDataByTableAndIds(tableName, batchIds);
                        log.info("表 {} 批量查询完成，返回 {} 条记录", tableName, result.size());
                        return result;
                    } catch (Exception e) {
                        log.error("表 {} 批量查询失败", tableName, e);
                        return new ArrayList<LinkedInUser>();
                    }
                }, executorService);
                
                futures.add(future);
            }
        }
        
        // 等待所有查询完成并合并结果
        List<LinkedInUser> results = futures.stream()
                .map(CompletableFuture::join)
                .flatMap(List::stream)
                .collect(Collectors.toList());
        
        // 关闭线程池
        executorService.shutdown();
        try {
            if (!executorService.awaitTermination(30, TimeUnit.SECONDS)) {
                executorService.shutdownNow();
            }
        } catch (InterruptedException e) {
            executorService.shutdownNow();
            Thread.currentThread().interrupt();
        }
        
        log.info("批量优化查询完成，共获取 {} 条记录", results.size());
        return results;
    }

    @Override
    public Page<LinkedInUserEs> searchByKeywordFromES(String keyword, int offset, int limit,
                                                     String companyName, String jobTitle, String industry,
                                                     String location, String contactMethod, String companySize) {
        log.info("ES关键词搜索: keyword={}, offset={}, limit={}, companyName={}, jobTitle={}, industry={}, location={}, contactMethod={}, companySize={}",
                keyword, offset, limit, companyName, jobTitle, industry, location, contactMethod, companySize);

        try {
            // 执行ES筛选搜索
            Page<LinkedInUserEs> esPage = linkedInEsService.searchWithFilters(
                    keyword, companyName, jobTitle, industry, location, contactMethod, companySize, offset, limit);

            List<LinkedInUserEs> records = esPage.getRecords();
            changeMysqlName(records);

            log.info("ES关键词搜索完成，返回 {} 条记录，总数: {}", esPage.getRecords().size(), esPage.getTotal());
            return esPage;

        } catch (Exception e) {
            log.error("ES关键词搜索失败", e);
            // 返回空结果页面
            Page<LinkedInUserEs> emptyPage = new Page<>();
            emptyPage.setCurrent(offset / limit + 1);
            emptyPage.setSize(limit);
            emptyPage.setTotal(0);
            emptyPage.setRecords(new ArrayList<>());
            return emptyPage;
        }
    }

    private static void changeMysqlName(List<LinkedInUserEs> records) {
        for (LinkedInUserEs record : records){
            String mt = record.getMt();
            String[] split = mt.split("_");
            String newMT = split[2];
            record.setMt(newMT);
        }
    }

    @Override
    public long countByKeywordFromES(String keyword, String companyName, String jobTitle, String industry,
                                    String location, String contactMethod, String companySize) {
        log.info("ES关键词搜索计数: keyword={}, companyName={}, jobTitle={}, industry={}, location={}, contactMethod={}, companySize={}",
                keyword, companyName, jobTitle, industry, location, contactMethod, companySize);

        try {
            // 执行ES筛选计数
            long count = linkedInEsService.countWithFilters(
                    keyword, companyName, jobTitle, industry, location, contactMethod, companySize);

            log.info("ES关键词搜索计数完成，总数: {}", count);
            return count;

        } catch (Exception e) {
            log.error("ES关键词搜索计数失败", e);
            return 0L;
        }
    }

    @Override
    public Map<String, List<String>> getLinkedInFilterOptionsFromES(LinkedInQueryDTO queryDTO) {
        log.info("获取LinkedIn用户筛选选项列表（从ES）: {}", queryDTO);

        try {
            // 构建ES查询条件
            LinkedInUserEs linkedInUserEs = buildLinkedInUserEs(queryDTO);

            // 执行ES查询获取筛选选项
            Map<String, List<String>> filterOptions = linkedInEsService.queryLinkedInUserMapList(linkedInUserEs);

            log.info("获取筛选选项完成，行业数: {}, 位置数: {}, 公司规模数: {}, 国家数: {}",
                    filterOptions.getOrDefault("industries", new ArrayList<>()).size(),
                    filterOptions.getOrDefault("locations", new ArrayList<>()).size(),
                    filterOptions.getOrDefault("companySizes", new ArrayList<>()).size(),
                    filterOptions.getOrDefault("countries", new ArrayList<>()).size());

            return filterOptions;

        } catch (Exception e) {
            log.error("获取LinkedIn用户筛选选项失败", e);
            return new HashMap<>();
        }
    }

    // ==================== 两阶段查询方法实现 ====================

    @Override
    public List<LinkedInUser> findByCompanyAndJobFromES(String companyName, String jobTitle, int limit) {
        log.info("两阶段查询 - 公司+职位: companyName={}, jobTitle={}, limit={}", companyName, jobTitle, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByCompanyAndJob(companyName, jobTitle, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - 公司+职位", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> findByNameAndCompanyFromES(String firstName, String lastName, String companyName, int limit) {
        log.info("两阶段查询 - 姓名+公司: firstName={}, lastName={}, companyName={}, limit={}", firstName, lastName, companyName, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByNameAndCompany(firstName, lastName, companyName, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - 姓名+公司", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> findByNameAndEmailFromES(String firstName, String lastName, String email, int limit) {
        log.info("两阶段查询 - 姓名+邮箱: firstName={}, lastName={}, email={}, limit={}", firstName, lastName, email, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByNameAndEmail(firstName, lastName, email, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - 姓名+邮箱", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> findByPhoneAndNameFromES(String phoneNumber, String firstName, String lastName, int limit) {
        log.info("两阶段查询 - 手机号+姓名: phoneNumber={}, firstName={}, lastName={}, limit={}", phoneNumber, firstName, lastName, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByPhoneAndName(phoneNumber, firstName, lastName, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - 手机号+姓名", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> findByEmailFromES(String email, int limit) {
        log.info("两阶段查询 - 邮箱精确匹配: email={}, limit={}", email, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByEmail(email, limit, 0);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - 邮箱精确匹配", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> findByLinkedInUrlFromES(String linkedinUrl, int limit) {
        log.info("两阶段查询 - LinkedIn URL: linkedinUrl={}, limit={}", linkedinUrl, limit);

        try {
            // 第一阶段：ES查询获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByLinkedInUrl(linkedinUrl, limit);
            log.info("第一阶段ES查询完成，获取到 {} 条标识信息", identifiers.size());

            if (identifiers.isEmpty()) {
                return new ArrayList<>();
            }

            // 第二阶段：数据库查询完整数据
            List<LinkedInUser> completeData = getCompleteDataFromDB(identifiers);
            log.info("第二阶段数据库查询完成，获取到 {} 条完整数据", completeData.size());

            // 数据脱敏处理
            for (LinkedInUser data : completeData) {
                desensitizationLinkedIn(data);
            }

            return completeData;

        } catch (Exception e) {
            log.error("两阶段查询失败 - LinkedIn URL", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<LinkedInUser> searchLinkedInUsersWithEmail(String searchKeyword, int offset, int size) {
        List<LinkedInUserEs> linkedInUserEsList = linkedInEsService.searchLinkedInUsersWithEmail(searchKeyword, offset, size);

        List<LinkedInUser> result = new ArrayList<>();
        for (LinkedInUserEs linkedInUserEs : linkedInUserEsList) {
            String mId = linkedInUserEs.getMId();
            String[] split = linkedInUserEs.getMt().split("_");
            String mt = split[2];
            LinkedInUser linkedInData = getLinkedInData(Long.parseLong(mId), Long.parseLong(mt));
            result.add(linkedInData);
        }

        return result;
    }

    @Override
    public Long countLinkedInUsersWithEmail(String searchKeyword) {
        return linkedInEsService.countLinkedInUsersWithEmail(searchKeyword);
    }

    @Override
    public List<LinkedInUser> searchLinkedInUsersWithPhone(String searchKeyword, int offset, int size) {
        List<LinkedInUserEs> linkedInUserEsList = linkedInEsService.searchLinkedInUsersWithPhone(searchKeyword, offset, size);

        List<LinkedInUser> result = new ArrayList<>();
        for (LinkedInUserEs linkedInUserEs : linkedInUserEsList) {
            String mId = linkedInUserEs.getMId();
            String[] split = linkedInUserEs.getMt().split("_");
            String mt = split[2];
            LinkedInUser linkedInData = getLinkedInData(Long.parseLong(mId), Long.parseLong(mt));
            result.add(linkedInData);
        }

        return result;
    }

    @Override
    public Long countLinkedInUsersWithPhone(String searchKeyword) {
        return linkedInEsService.countLinkedInUsersWithPhone(searchKeyword);
    }

    @Override
    public List<Map<String, Object>> searchByEmail(String value, int size, int offset) {
        List<Map<String, String>> maps = linkedInEsService.searchByEmail(value, size, offset);

        // 批量搜索
        return batchQueryLinkedInFromMysql(maps);

    }

    @Override
    public List<Map<String, Object>> searchByPhone(String value, int size, int offset) {
        List<Map<String, String>> maps = linkedInEsService.searchByPhone(value, size, offset);

        // 批量搜索
        return batchQueryLinkedInFromMysql(maps);
    }

    @Override
    public Long countWithEmail(String value) {
        LinkedInUserEs linkedInUserEs = new LinkedInUserEs();
        linkedInUserEs.setEmails(value);
        return linkedInEsService.countLinkedInUsers(linkedInUserEs);
    }

    @Override
    public Long countWithPhone(String value) {
        LinkedInUserEs linkedInUserEs = new LinkedInUserEs();
        linkedInUserEs.setPhoneNumbers(value);
        return linkedInEsService.countLinkedInUsers(linkedInUserEs);
    }

    @Override
    public List<Map<String, Object>> findByDomain(String domain, int size, int offset) {
        log.info("通过域名查询LinkedIn: domain={}, size={}, offset={}", domain, size, offset);
        
        try {
            // 第一阶段：从ES获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.findByDomain(domain, size, offset);
            
            if (identifiers.isEmpty()) {
                log.info("ES查询未找到匹配数据");
                return new ArrayList<>();
            }
            
            log.info("ES查询完成，获取到{}条标识信息", identifiers.size());
            
            // 第二阶段：批量从数据库查询完整数据
            return batchQueryLinkedInFromMysql(identifiers);
        } catch (Exception e) {
            log.error("通过域名查询LinkedIn失败: domain={}", domain, e);
            return new ArrayList<>();
        }
    }
    
    /**
     * 批量从MySQL查询LinkedIn数据
     */
    private List<Map<String, Object>> batchQueryLinkedInFromMysql(List<Map<String, String>> identifiers) {
        List<Map<String, Object>> results = new ArrayList<>();
        jdbcTemplate.setDataSource(linkedInDataSource.dataSource());
        
        try {
            // 按表编号分组
            Map<String, List<String>> tableIdMap = new HashMap<>();
            for (Map<String, String> identifier : identifiers) {
                String mysqlTable = identifier.get("mysqlTable");
                String id = identifier.get("id");
                
                if (mysqlTable != null && id != null) {
                    // 从mysqlTable中提取表编号，例如 "ly_user_1" -> "1"
                    String[] split = mysqlTable.split("_");
                    if (split.length >= 3) {
                        String tableNum = split[2];
                        tableIdMap.computeIfAbsent(tableNum, k -> new ArrayList<>()).add(id);
                    }
                }
            }
            
            // 对每个表进行批量ID查询
            for (Map.Entry<String, List<String>> entry : tableIdMap.entrySet()) {
                String tableNum = entry.getKey();
                List<String> ids = entry.getValue();
                
                if (ids.isEmpty()) {
                    continue;
                }
                
                try {
                    // 批量查询 - 使用ShardingSphere Hint强制指定数据源
                    String tableName = "ly_user_" + tableNum;
                    String placeholders = String.join(",", ids.stream().map(id -> "?").toList());
                    String sql = String.format(
                            "/* ShardingSphere hint: dataSourceName=ly_db */ SELECT * FROM ly_db.%s WHERE id IN (%s)",
                            tableName, placeholders
                    );
                    
                    Object[] params = ids.toArray();
                    List<Map<String, Object>> tableResults = jdbcTemplate.queryForList(sql, params);
                    results.addAll(tableResults);
                    
                    log.info("从表ly_user_{}批量查询{}条记录，实际查询到{}条", tableNum, ids.size(), tableResults.size());
                } catch (Exception e) {
                    log.error("批量查询表ly_user_{}失败: ", tableNum, e);
                }
            }
            
            return results;
        } catch (Exception e) {
            log.error("批量查询LinkedIn数据失败", e);
            return new ArrayList<>();
        }
    }

    @Override
    public long countByDomain(String domain) {
        LinkedInUserEs linkedInUserEs = new LinkedInUserEs();
        linkedInUserEs.setCompanyWebsite(domain);
        return linkedInEsService.countLinkedInUsers(linkedInUserEs);
    }

    @Override
    public List<Map<String, Object>> searchLinkedInUsersByKeyword(String keyword, int offset, int size) {
        List<Map<String, String>> maps = linkedInEsService.findByKeyword(keyword, size, offset);

        return batchQueryLinkedInFromMysql(maps);
    }

    @Override
    public List<Map<String, Object>> searchLinkedInByNameOrUrl(String keyword, int offset, int size) {
        List<Map<String, String>> maps = linkedInEsService.searchLinkedInByNameOrUrl(keyword, size, offset);

        return batchQueryLinkedInFromMysql(maps);
    }
    
    @Override
    public List<Map<String, Object>> searchByLinkedInUrlAndName(String linkedinUrl, String name, int offset, int size) {
        log.info("查询LinkedIn数据: linkedinUrl={}, name={}, offset={}, size={}", linkedinUrl, name, offset, size);
        
        try {
            // 第一阶段：从ES获取标识信息
            List<Map<String, String>> identifiers = linkedInEsService.searchByLinkedInUrlAndName(linkedinUrl, name, size, offset);
            
            if (identifiers.isEmpty()) {
                log.info("ES查询未找到匹配数据");
                return new ArrayList<>();
            }
            
            log.info("ES查询完成，获取到{}条标识信息", identifiers.size());
            
            // 第二阶段：批量从数据库查询完整数据
            List<Map<String, Object>> results = batchQueryLinkedInFromMysql(identifiers);
            
            log.info("数据库查询完成，获取到{}条完整数据", results.size());
            return results;
            
        } catch (Exception e) {
            log.error("查询LinkedIn数据失败: linkedinUrl={}, name={}", linkedinUrl, name, e);
            return new ArrayList<>();
        }
    }

    @Override
    public long countLinkedInUsersByKeyword(String keyword) {
        LinkedInUserEs linkedInUserEs = new LinkedInUserEs();

        linkedInUserEs.setFullName(keyword);
        linkedInUserEs.setLinkedinUrl(keyword);
        linkedInUserEs.setCompanyName(keyword);
        linkedInUserEs.setTwitterUserName(keyword);
        linkedInUserEs.setFacebookUrl(keyword);
        linkedInUserEs.setGithubUrl(keyword);
        linkedInUserEs.setTwitterUrl(keyword);
        return linkedInEsService.countLinkedInUsersByKeyword(linkedInUserEs);
    }

    @Override
    public List<String> searchCompanies(String company) {
        return linkedInEsService.searchCompanies(company);
    }

    @Override
    public List<String> searchJobTitles(String jobTitles) {
        return linkedInEsService.searchJobTitles(jobTitles);
    }

    @Override
    public List<String> searchIndustries(String industry) {
        return linkedInEsService.searchIndustries(industry);
    }

    @Override
    public List<String> searchLocations(String location) {
        return linkedInEsService.searchLocations(location);
    }

    @Override
    public List<LinkedInUser> searchLinkedInData(String companyName, String fullName, String website, Integer matchType) {
        log.info("批量搜索LinkedIn: 公司名称={}, 人名={}, 网址={}, 匹配类型={}", companyName, fullName, website, matchType);
        
        try {
            // 第一阶段es查询
            List<LinkedInUserEs> linkedInUserEsList;
            if (matchType == 1) {
                linkedInUserEsList = linkedInEsService.searchLinkedInDataByPhone(companyName, fullName, website);
            } else {
                linkedInUserEsList = linkedInEsService.searchLinkedInDataByEmail(companyName, fullName, website);
            }

            log.info("ES查询到{}条LinkedIn数据，开始从MySQL批量获取详细信息", linkedInUserEsList.size());

            if (linkedInUserEsList.isEmpty()) {
                return new ArrayList<>();
            }

            // 按表名分组
            Map<Long, List<Long>> tableToIds = new HashMap<>();
            for (LinkedInUserEs linkedInUserEs : linkedInUserEsList) {
                try {
                    String mId = linkedInUserEs.getMId();
                    String[] split = linkedInUserEs.getMt().split("_");
                    Long tableNum = Long.parseLong(split[2]);
                    Long id = Long.parseLong(mId);
                    
                    tableToIds.computeIfAbsent(tableNum, k -> new ArrayList<>()).add(id);
                } catch (Exception e) {
                    log.error("解析LinkedIn表名和ID失败: mt={}, mId={}", linkedInUserEs.getMt(), linkedInUserEs.getMId(), e);
                }
            }
            
            log.info("数据分布在{}个不同的表中，开始并行批量查询", tableToIds.size());

            // 使用线程池并行查询不同的表
            ExecutorService queryExecutor = Executors.newFixedThreadPool(Math.min(tableToIds.size(), 20));
            List<CompletableFuture<List<LinkedInUser>>> queryTasks = new ArrayList<>();
            
            for (Map.Entry<Long, List<Long>> entry : tableToIds.entrySet()) {
                Long tableNum = entry.getKey();
                List<Long> ids = entry.getValue();
                
                CompletableFuture<List<LinkedInUser>> task = CompletableFuture.supplyAsync(() -> {
                    try {
                        return batchGetLinkedInDataByTableAndIds(tableNum, ids);
                    } catch (Exception e) {
                        log.error("批量查询表ly_user_{}失败，包含{}条记录", tableNum, ids.size(), e);
                        return new ArrayList<>();
                    }
                }, queryExecutor);
                
                queryTasks.add(task);
            }
            
            // 等待所有查询完成
            CompletableFuture<Void> allTasks = CompletableFuture.allOf(
                queryTasks.toArray(new CompletableFuture[0])
            );
            allTasks.join();
            
            // 合并结果
            List<LinkedInUser> result = new ArrayList<>();
            for (CompletableFuture<List<LinkedInUser>> task : queryTasks) {
                try {
                    List<LinkedInUser> results = task.get();
                    result.addAll(results);
                } catch (Exception e) {
                    log.error("获取查询结果失败", e);
                }
            }
            
            // 关闭线程池
            queryExecutor.shutdown();
            
            int successCount = result.size();
            int failCount = linkedInUserEsList.size() - successCount;

            log.info("LinkedIn数据处理完成: ES查询{}条, MySQL成功获取{}条, 失败{}条", 
                     linkedInUserEsList.size(), successCount, failCount);
            return result;
            
        } catch (Exception e) {
            log.error("LinkedIn库查询失败", e);
            return new ArrayList<>();
        }
    }

    private Map<String, Object> getLinkedInByIdAndTable(long id, long table) {
        jdbcTemplate.setDataSource(linkedInDataSource.dataSource());
        // 单条记录查询
        id = Integer.parseInt(String.valueOf(id));

        String tableName = "ly_user_" + table;

        String sql =
                "/* ShardingSphere hint: dataSourceName=facebook_txt */ " +
                        "SELECT * FROM ly_db." + tableName + " WHERE id = ?";

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, id);

        return maps.get(0);
    }

    // ==================== 私有辅助方法 ====================

    /**
     * 构建LinkedInUserEs查询条件
     */
    private LinkedInUserEs buildLinkedInUserEs(LinkedInQueryDTO queryDTO) {
        LinkedInUserEs linkedInUserEs = new LinkedInUserEs();

        // 姓名搜索
        if (StringUtils.hasText(queryDTO.getFullName())) {
            linkedInUserEs.setFullName(queryDTO.getFullName());
        }

        // 设置公司名称筛选
        if (StringUtils.hasText(queryDTO.getCompanyName())) {
            linkedInUserEs.setCompanyName(queryDTO.getCompanyName());
        }

        // 设置职位筛选
        if (StringUtils.hasText(queryDTO.getJobTitle())) {
            linkedInUserEs.setJobTitle(queryDTO.getJobTitle());
        }

        // 设置行业筛选
        if (StringUtils.hasText(queryDTO.getIndustry())) {
            linkedInUserEs.setCompanyIndustry(queryDTO.getIndustry());
        }

        // 设置城市筛选
        if (StringUtils.hasText(queryDTO.getCountry())) {
            linkedInUserEs.setLocation(queryDTO.getCountry());
        }

        // 设置公司规模筛选
        if (StringUtils.hasText(queryDTO.getCompanySize())) {
            linkedInUserEs.setCompanySize(queryDTO.getCompanySize());
        }

        // 设置联系方式筛选
        if (StringUtils.hasText(queryDTO.getContactMethod())) {
            linkedInUserEs.setContactMethod(queryDTO.getContactMethod());
        }

        // 设置国家筛选
        if (StringUtils.hasText(queryDTO.getCountry())) {
            linkedInUserEs.setCountry(queryDTO.getCountry());
        }


        // 设置导出限制
        if (queryDTO.getLimit() != null && queryDTO.getLimit() > 0) {
            linkedInUserEs.setLimit(queryDTO.getLimit());
        }

        return linkedInUserEs;
    }

    /**
     * 第二阶段：根据标识信息从数据库查询完整数据
     */
    private List<LinkedInUser> getCompleteDataFromDB(List<Map<String, String>> identifiers) {
        log.info("第二阶段：根据标识信息从数据库查询完整数据，标识数量: {}", identifiers.size());

        if (identifiers == null || identifiers.isEmpty()) {
            return new ArrayList<>();
        }

        List<LinkedInUser> completeData = new ArrayList<>();

        try {
            // 按表名分组，批量查询提高效率
            Map<String, List<String>> tableGroups = new HashMap<>();

            for (Map<String, String> identifier : identifiers) {
                String tableName = identifier.get("mysqlTable");
                String recordId = identifier.get("id");

                if (StringUtils.hasText(tableName) && StringUtils.hasText(recordId)) {
                    tableGroups.computeIfAbsent(tableName, k -> new ArrayList<>()).add(recordId);
                }
            }

            log.info("按表名分组后，需要查询 {} 个表", tableGroups.size());

            // 遍历每个表，批量查询数据
            for (Map.Entry<String, List<String>> entry : tableGroups.entrySet()) {
                String tableName = entry.getKey();
                List<String> recordIds = entry.getValue();

                log.info("查询表 {} 中的 {} 条记录", tableName, recordIds.size());

                // 根据表名和ID列表查询完整数据
                List<LinkedInUser> tableData = queryCompleteDataByTableAndIds(tableName, recordIds);
                completeData.addAll(tableData);

                log.info("从表 {} 查询到 {} 条完整数据", tableName, tableData.size());
            }

            log.info("第二阶段查询完成，总共获取到 {} 条完整数据", completeData.size());

            return completeData;

        } catch (Exception e) {
            log.error("从数据库查询完整LinkedIn数据时发生错误", e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据表名和ID列表查询完整的LinkedIn数据
     */
    private List<LinkedInUser> queryCompleteDataByTableAndIds(String tableName, List<String> recordIds) {
        log.info("查询表 {} 中ID为 {} 的记录", tableName, recordIds);

        try {
            List<LinkedInUser> results = new ArrayList<>();

            // 建立数据库连接
            jdbcTemplate.setDataSource(linkedInDataSource.dataSource());

            // 批量查询（如果支持的话）
            if (recordIds.size() > 1) {
                String placeholders = String.join(",", Collections.nCopies(recordIds.size(), "?"));
                String sql = "/* ShardingSphere hint: dataSourceName=ly_db */ " +
                        "SELECT * FROM ly_db." + tableName + " WHERE id IN (" + placeholders + ")";

                // 将字符串ID转换为整数（如果数据库中的id是整数类型）
                List<Integer> intIds = recordIds.stream()
                        .map(Integer::parseInt)
                        .collect(Collectors.toList());

                results = jdbcTemplate.query(sql, intIds.toArray(), (rs, rowNum) -> getLinkedInUser(rs));

                log.info("批量查询返回 {} 条记录", results.size());
                return results;

            } else {
                // 单条记录查询
                Integer id = Integer.parseInt(recordIds.get(0));

                String sql =
                        "/* ShardingSphere hint: dataSourceName=facebook_txt */ " +
                        "SELECT * FROM ly_db." + tableName + " WHERE id = ?";

                List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, id);

                LinkedInUser user = new LinkedInUser();
                if (!maps.isEmpty()) {
                    maps.forEach(map -> {
                        user.setId(getLong(map, "id"));
                        user.setFullName(getString(map, "full_name"));
                        user.setIndustry(getString(map, "industry"));
                        user.setJobTitle(getString(map, "job_title"));
                        user.setSubRole(getString(map, "sub_role"));
                        user.setIndustry2(getString(map, "industry2"));
                        user.setEmails(getString(map, "emails"));
                        user.setMobile(getString(map, "mobile"));
                        user.setPhoneNumbers(getString(map, "phone_numbers"));
                        user.setCompanyName(getString(map, "company_name"));
                        user.setCompanyIndustry(getString(map, "company_industry"));
                        user.setCompanyWebsite(getString(map, "company_website"));
                        user.setCompanySize(getString(map, "company_size"));
                        user.setCompanyFounded(getInt(map, "company_founded"));
                        user.setLocation(getString(map, "location"));
                        user.setLocality(getString(map, "locality"));
                        user.setMetro(getString(map, "metro"));
                        user.setRegion(getString(map, "region"));
                        user.setSkills(getString(map, "skills"));
                        user.setFirstName(getString(map, "first_name"));
                        user.setMiddleInitial(getString(map, "middle_initial"));
                        user.setMiddleName(getString(map, "middle_name"));
                        user.setLastName(getString(map, "last_name"));
                        user.setBirthYear(getInt(map, "birth_year"));
                        user.setBirthDate(getString(map, "birth_date"));
                        user.setGender(getString(map, "gender"));
                        user.setLinkedinUrl(getString(map, "linkedin_url"));
                        user.setLinkedinUserName(getString(map, "linkedin_user_name"));
                        user.setFacebookUrl(getString(map, "facebook_url"));
                        user.setFacebookUserName(getString(map, "facebook_user_name"));
                        user.setTwitterUrl(getString(map, "twitter_url"));
                        user.setTwitterUserName(getString(map, "twitter_user_name"));
                        user.setGithubUrl(getString(map, "github_url"));
                        user.setGithubUserName(getString(map, "github_user_name"));
                        user.setCompanyLinkedinUrl(getString(map, "company_linkedin_url"));
                        user.setCompanyFacebookUrl(getString(map, "company_facebook_url"));
                        user.setCompanyTwitterUrl(getString(map, "company_twitter_url"));
                        user.setCompanyLocationName(getString(map, "company_location_name"));
                        user.setCompanyLocationLocality(getString(map, "company_location_locality"));
                        user.setCompanyLocationMetro(getString(map, "company_location_metro"));
                        user.setCompanyLocationRegion(getString(map, "company_location_region"));
                        user.setCompanyLocationGeo(getString(map, "company_location_geo"));
                        user.setCompanyLocationStreetAddress(getString(map, "company_location_street_address"));
                        user.setCompanyLocationAddressLine2(getString(map, "company_location_address_line2"));
                        user.setCompanyLocationPostalCode(getString(map, "company_location_postal_code"));
                        user.setLocationCountry(getString(map, "company_location_country"));
                        user.setLastUpdated(getString(map, "last_updated"));
                        user.setStartDate(getString(map, "start_date"));
                        user.setJobSummary(getString(map, "job_summary"));
                        user.setLocationCountry(getString(map, "location_country"));
                        user.setLocationContinent(getString(map, "location_continent"));
                        user.setStreetAddress(getString(map, "street_address"));
                        user.setAddressLine2(getString(map, "address_line2"));
                        user.setPostalCode(getString(map, "postal_code"));
                        user.setLocationGeo(getString(map, "location_geo"));
                        user.setLinkedinConnections(getString(map, "linkedin_connections"));
                        user.setInferredSalary(getString(map, "inferred_salary"));
                        user.setYearsExperience(getString(map, "years_experience"));
                        user.setSummary(getString(map, "summary"));
                        user.setCountries(getString(map, "countries"));
                        user.setInterests(getString(map, "interests"));
                    });

                    results.add(user);
                }


                return results;
            }

        } catch (Exception e) {
            log.error("查询表 {} 中的记录时发生错误", tableName, e);

            // 如果批量查询失败，回退到逐个查询模式
            List<LinkedInUser> results = new ArrayList<>();
            for (String recordId : recordIds) {
                try {
                    Integer id = Integer.parseInt(recordId);
                    LinkedInUser user = getById(id);
                    if (user != null) {
                        results.add(user);
                    }
                } catch (Exception ex) {
                    log.warn("查询表 {} 中ID {} 的记录时出错: {}", tableName, recordId, ex.getMessage());
                }
            }
            return results;
        }
    }

    private Integer getInt(Map<String, Object> map, String key) {
        if (map == null || key == null) return null;
        Object value = map.get(key);
        if (value == null) return null;

        try {
            if (value instanceof Number) {
                return ((Number) value).intValue();
            }
            return Integer.getInteger(value.toString());
        } catch (NumberFormatException e) {
            log.warn("无法转换为整数: key={}, value={}", key, value);
            return null;
        }
    }

    private Long getLong(Map<String, Object> map, String key) {
        if (map == null || key == null) return null;
        Object value = map.get(key);
        if (value == null) return null;

        try {
            if (value instanceof Number) {
                return ((Number) value).longValue();
            }
            return Long.parseLong(value.toString());
        } catch (NumberFormatException e) {
            log.warn("无法转换为整数: key={}, value={}", key, value);
            return null;
        }
    }

    /**
     * 安全地从Map中获取字符串值
     *
     * @param row 数据行
     * @param key 键名
     * @return 字符串值
     */
    private String getString(Map<String, Object> row, String key) {
        Object value = row.get(key);
        return value != null ? value.toString() : null;
    }

    @NotNull
    private static LinkedInUser getLinkedInUser(ResultSet rs) throws SQLException {
        LinkedInUser user = new LinkedInUser();
        user.setId(rs.getLong("id"));
        user.setFullName(rs.getString("full_name"));
        user.setIndustry(rs.getString("industry"));
        user.setJobTitle(rs.getString("job_title"));
        user.setSubRole(rs.getString("sub_role"));
        user.setIndustry2(rs.getString("industry2"));
        user.setEmails(rs.getString("emails"));
        user.setMobile(rs.getString("mobile"));
        user.setPhoneNumbers(rs.getString("phone_numbers"));
        user.setCompanyName(rs.getString("company_name"));
        user.setCompanyIndustry(rs.getString("company_industry"));
        user.setCompanyWebsite(rs.getString("company_website"));
        user.setCompanySize(rs.getString("company_size"));
        user.setCompanyFounded(rs.getInt("company_founded"));
        user.setLocation(rs.getString("location"));
        user.setLocality(rs.getString("locality"));
        user.setMetro(rs.getString("metro"));
        user.setRegion(rs.getString("region"));
        user.setSkills(rs.getString("skills"));
        user.setFirstName(rs.getString("first_name"));
        user.setMiddleInitial(rs.getString("middle_initial"));
        user.setMiddleName(rs.getString("middle_name"));
        user.setLastName(rs.getString("last_name"));
        user.setBirthYear(rs.getInt("birth_year"));
        user.setBirthDate(rs.getString("birth_date"));
        user.setGender(rs.getString("gender"));
        user.setLinkedinUrl(rs.getString("linkedin_url"));
        user.setLinkedinUserName(rs.getString("linkedin_user_name"));
        user.setFacebookUrl(rs.getString("facebook_url"));
        user.setFacebookUserName(rs.getString("facebook_user_name"));
        user.setTwitterUrl(rs.getString("twitter_url"));
        user.setTwitterUserName(rs.getString("twitter_user_name"));
        user.setGithubUrl(rs.getString("github_url"));
        user.setGithubUserName(rs.getString("github_user_name"));
        user.setCompanyLinkedinUrl(rs.getString("company_linkedin_url"));
        user.setCompanyFacebookUrl(rs.getString("company_facebook_url"));
        user.setCompanyTwitterUrl(rs.getString("company_twitter_url"));
        user.setCompanyLocationName(rs.getString("company_location_name"));
        user.setCompanyLocationLocality(rs.getString("company_location_locality"));
        user.setCompanyLocationMetro(rs.getString("company_location_metro"));
        user.setCompanyLocationRegion(rs.getString("company_location_region"));
        user.setCompanyLocationGeo(rs.getString("company_location_geo"));
        user.setCompanyLocationStreetAddress(rs.getString("company_location_street_address"));
        user.setCompanyLocationAddressLine2(rs.getString("company_location_address_line2"));
        user.setCompanyLocationPostalCode(rs.getString("company_location_postal_code"));
        user.setLocationCountry(rs.getString("company_location_country"));
        user.setLastUpdated(rs.getString("last_updated"));
        user.setStartDate(rs.getString("start_date"));
        user.setJobSummary(rs.getString("job_summary"));
        user.setLocationCountry(rs.getString("location_country"));
        user.setLocationContinent(rs.getString("location_continent"));
        user.setStreetAddress(rs.getString("street_address"));
        user.setAddressLine2(rs.getString("address_line2"));
        user.setPostalCode(rs.getString("postal_code"));
        user.setLocationGeo(rs.getString("location_geo"));
        user.setLinkedinConnections(rs.getString("linkedin_connections"));
        user.setInferredSalary(rs.getString("inferred_salary"));
        user.setYearsExperience(rs.getString("years_experience"));
        user.setSummary(rs.getString("summary"));
        user.setCountries(rs.getString("countries"));
        user.setInterests(rs.getString("interests"));
        return user;
    }

}